-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO PROCESS batch_insurance2_t 


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION batch_insurance2_updating_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN


SELECT INTO v_batch_run_date our_run_date from temp_batch_insurance2_t limit 1;
SELECT INTO v_cust_id cust_id from temp_batch_insurance2_t limit 1;

PERFORM ml_upd_batch_status_insurance2_new_f(v_cust_id,v_batch_run_date,1,3,'batch_insurance2_t UPDATING START');



-- STEP 1 - MERGING WITH MEDICARE CONTRACT TABLE 

RAISE NOTICE 'START MERGING batch_insurance2_t TABLE WITH MEDICARE CONTRACT TABLE ';

UPDATE batch_insurance2_t
SET
 c_insurance_desc = (CASE WHEN a.medicare_insurance_desc IS NOT NULL THEN 'MEDICARE HMO '||a.medicare_contract||' :'||a.medicare_insurance_desc::varchar(75) 
                          ELSE                                            'MEDICARE HMO '||a.medicare_contract::varchar(75)
                          END),
 insurance_addr  = (CASE WHEN a.medicare_contact_addr2 IS NOT NULL THEN a.medicare_contact_addr1||' - '||a.medicare_contact_addr2::varchar(80)
                            ELSE                                           a.medicare_contact_addr1::varchar(80)
                            END),
 insurance_city          = a.medicare_contact_city,
 insurance_state         = a.medicare_contact_state,
 insurance_zip           = a.medicare_contact_zip, 
 insurance_contact_name  = a.medicare_contact_name,
 insurance_contact_phone = a.medicare_contact_phone

  FROM
     insurance0_medicare_contracts_t as a 
  WHERE
    (batch_insurance2_t.carrier_code = '00024')
    AND
    (batch_insurance2_t.c_insurance_desc = a.medicare_contract)
  ;

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH MERGING batch_insurance2_t TABLE WITH MEDICARE CONTRACT TABLE : %', v_status;


-- END OF STEP 1




-- STEP 2 - UPDATING SOME FIELDS; 

RAISE NOTICE 'START UPDATING batch_insurance2_t TABLE ';


  UPDATE batch_insurance2_t
  SET
     active_flag  = (CASE WHEN suppress_flag in (100,110,1100,1110,10100,11100,11110)    THEN 0
                          ELSE                                                                active_flag
                          END),
     c_insurance_desc  = (CASE WHEN carrier_code = '00024'     AND
                                    c_insurance_desc = 'XXXXX'                             THEN NULL 
                               WHEN carrier_code = '00024'     AND
                                    substring(c_insurance_desc from 1 for 8) <> 'MEDICARE' THEN 'MEDICARE HMO '||c_insurance_desc::varchar(75)
                               ELSE                                                             c_insurance_desc
                               END),
    insurance_desc = (CASE WHEN carrier_code = '00024' THEN 'MEDICARE'
                           ELSE                             insurance_desc
                           END),
    quality = (CASE WHEN processing_ssn_flag IS NOT NULL                                                                   AND
                         emdeon_ins_type <> 'D'                                                                            AND
			 patient_dob IS NOT NULL                                                                           AND
			 dob = patient_dob                                                                                 AND
			 lname IS NOT NULL                                                                                 AND
			 regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g')
			   = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
			 fname IS NOT NULL                                                                                 AND
			 substring(regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 7)
			  = substring(regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 7)   AND
			 state IS NOT NULL                                                                                 AND
			 (
			    (
			      subscriber_state IS NOT NULL                                                 AND
			      state = subscriber_state
			    )
			    OR
			    (
			      patient_state IS NOT NULL                                                    AND
			      state = patient_state
			    )
			 )                                                                                                 AND
                        ( ssn IS NULL or patient_ssn IS NULL or ssn=patient_ssn )                                              THEN  65 

                    WHEN processing_ssn_flag IS NOT NULL                                                                   AND
                         emdeon_ins_type = 'D'                                                                             AND
                         patient_dob IS NOT NULL                                                                           AND
                         dob = patient_dob                                                                                 AND
                         (
                           (
                             lname IS NOT NULL                                                            AND
                             regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                               = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')     AND
                             fname IS NOT NULL                                                            AND
                             regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                               = regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                           )
                           OR
                           (
                             search_pattern = 'NAME/DOB'                                                  AND
                             lname IS NOT NULL                                                            AND
                             substring(regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 2)
                               = substring(regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 2)
                           )
                         )                                                                                                 AND
                         (ssn IS NULL or patient_ssn IS NULL or ssn=patient_ssn)                                               THEN  65

                    WHEN processing_ssn_flag IS NOT NULL                                                                   AND
                         lname IS NOT NULL                                                                                 AND
                         regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                           = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
                         patient_dob IS NOT NULL                                                                           AND
                         patient_dob = dob                                                                                 AND
                         (
                           (
                             subscriber_addr1 IS NOT NULL                                                 AND
                             addr1            IS NOT NULL                                                 AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(subscriber_addr1,'[ -]','','g') from 1 for 9)   AND
                             state IS NOT NULL                                                            AND
                             subscriber_state IS NOT NULL                                                 AND
                             state = subscriber_state
                           )
                           OR
                           (
                             patient_addr1 IS NOT NULL                                                    AND
                             addr1         IS NOT NULL                                                    AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(patient_addr1,'[ -]','','g') from 1 for 9)      AND
                             state IS NOT NULL                                                            AND
                             patient_state IS NOT NULL                                                    AND
                             state = patient_state
                           )
                         )                                                                                                 AND
                         (ssn IS NULL or patient_ssn IS NULL or ssn=patient_ssn)                                               THEN  65

                    WHEN processing_ssn_flag IS NOT NULL                                                                   AND
                         fname IS NOT NULL                                                                                 AND
                         regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                           = regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
                         patient_dob IS NOT NULL                                                                           AND
                         patient_dob = dob                                                                                 AND
                         (
                           (
                             subscriber_addr1 IS NOT NULL                                                 AND
                             addr1            IS NOT NULL                                                 AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(subscriber_addr1,'[ -]','','g') from 1 for 9)   AND
                             state IS NOT NULL                                                            AND
                             subscriber_state IS NOT NULL                                                 AND
                             state = subscriber_state
                           )
                           OR
                           (
                             patient_addr1 IS NOT NULL                                                    AND
                             addr1         IS NOT NULL                                                    AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(patient_addr1,'[ -]','','g') from 1 for 9)      AND
                             state IS NOT NULL                                                            AND
                             patient_state IS NOT NULL                                                    AND
                             state = patient_state
                           )
                         )                                                                                                 AND
                         (ssn IS NULL or patient_ssn IS NULL or ssn=patient_ssn)                                               THEN  65

		    WHEN processing_ssn_flag IS NOT NULL                                                                       THEN  7

	            WHEN quality = 9                                                                                       AND 
                         patient_dob IS NOT NULL                                                                           AND
                         dob = patient_dob                                                                                 AND
                         lname IS NOT NULL                                                                                 AND
                         regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') 
                           = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
                         fname IS NOT NULL                                                                                 AND
                         substring(regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 7) 
                           = substring(regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 7)      THEN  1

                    WHEN (quality in (7,8) or substring(ssn from 1 for 7) = substring(patient_ssn from 1 for 7))           AND
                         patient_dob IS NOT NULL                                                                           AND
                         dob = patient_dob                                                                                 AND
                         lname IS NOT NULL                                                                                 AND
                         regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') 
                           = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
                         fname IS NOT NULL                                                                                 AND
                         substring(regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 7) 
                           = substring(regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 7)      THEN  2

                    WHEN quality = 9                                                                                       AND
                         patient_dob IS NOT NULL                                                                           AND
                         dob = patient_dob                                                                                 AND
                         fname IS NOT NULL                                                                                 AND
                         (
                           (substring(regexp_replace(fname,'[ -]','','g') from 1 for 3)
                              = substring(regexp_replace(patient_fname,'[ -]','','g') from 1 for 3)  
                           )
                           OR
                           (substring(regexp_replace(lname,'[ -]','','g') from 1 for 3)
                              = substring(regexp_replace(patient_lname,'[ -]','','g') from 1 for 3)
                           )
                         )                                                                                                     THEN 3 
                    WHEN quality = 9                                                                                       AND
                         lname IS NOT NULL                                                                                 AND
                         regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') 
                           = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')                              THEN  4

                    WHEN patient_dob IS NOT NULL                                                                           AND  
                         dob = patient_dob                                                                                 AND
                         (
                           (
                             lname IS NOT NULL                                                            AND
                             regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') 
                               = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')     AND
                             fname IS NOT NULL                                                            AND
                             regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g') 
                               = regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g')    
                           )
                           OR
                           (
                             search_pattern = 'NAME/DOB'                                                  AND
                             lname IS NOT NULL                                                            AND
                             substring(regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 2)
                               = substring(regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 2)  
                           )
                         )
                                                                                                                           AND
                         (
                           (
                             patient_city IS NOT NULL                                                     AND
                             substring(regexp_replace(city,'[ -]','','g') from 1 for 7) 
                               = substring(regexp_replace(patient_city,'[ -]','','g') from 1 for 7)       AND
                             state IS NOT NULL                                                            AND
                             patient_state IS NOT NULL                                                    AND
                             state = patient_state                                                        
                           )
                           OR
                          (
                             subscriber_city IS NOT NULL                                                  AND
                             substring(regexp_replace(city,'[ -]','','g') from 1 for 7)
                               = substring(regexp_replace(subscriber_city,'[ -]','','g') from 1 for 7)    AND
                             state IS NOT NULL                                                            AND
                             subscriber_state IS NOT NULL                                                 AND
                             state = subscriber_state                                                     
                           )
                         )                                                                                                 AND 
                         (ssn IS NULL or patient_ssn IS NULL)                                                                   THEN  5

                    WHEN emdeon_ins_type <> 'D'                                                                            AND
                         patient_dob IS NOT NULL                                                                           AND
                         dob = patient_dob                                                                                 AND
                         lname IS NOT NULL                                                                                 AND
                         regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') 
                           = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
                         fname IS NOT NULL                                                                                 AND
                         regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g') 
                           = regexp_replace(patient_fname,'[ -]','','g')                                                   AND
                         state IS NOT NULL                                                                                 AND
                         (
                           (
                             subscriber_state IS NOT NULL                                                 AND
                             state = subscriber_state                                                     
                           )
                           OR
                           (
                             patient_state IS NOT NULL                                                    AND
                             state = patient_state                                                        
                           )
                         )                                                                                                 AND
                         (ssn IS NULL or patient_ssn IS NULL)                                                                   THEN  61

                    WHEN emdeon_ins_type = 'D'                                                                             AND 
                         patient_dob IS NOT NULL                                                                           AND
                         dob = patient_dob                                                                                 AND
                         (
                           (
                             lname IS NOT NULL                                                            AND
                             regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                               = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')     AND
                             fname IS NOT NULL                                                            AND
                             regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                               = regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                           )
                           OR
                           (
                             search_pattern = 'NAME/DOB'                                                  AND
                             lname IS NOT NULL                                                            AND
                             substring(regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 2)
                               = substring(regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g') from 1 for 2)
                           )
                         )                                                                                                 AND
                         (ssn IS NULL or patient_ssn IS NULL)                                                                   THEN  62 

                    WHEN lname IS NOT NULL                                                                                 AND 
                         regexp_replace(lname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                           = regexp_replace(patient_lname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
			 patient_dob IS NOT NULL                                                                           AND
			 patient_dob = dob                                                                                 AND
                         (
                           (
                             subscriber_addr1 IS NOT NULL                                                 AND
			     addr1            IS NOT NULL                                                 AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(subscriber_addr1,'[ -]','','g') from 1 for 9)   AND
                             state IS NOT NULL                                                            AND
                             subscriber_state IS NOT NULL                                                 AND
                             state = subscriber_state
                           )
                           OR
                           (
                             patient_addr1 IS NOT NULL                                                    AND
			     addr1         IS NOT NULL                                                    AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(patient_addr1,'[ -]','','g') from 1 for 9)      AND
                             state IS NOT NULL                                                            AND
                             patient_state IS NOT NULL                                                    AND
                             state = patient_state
                           )
                         )                                                                                                 AND
                         (ssn IS NULL or patient_ssn IS NULL)                                                                   THEN  63
                    WHEN fname IS NOT NULL                                                                                 AND
                         regexp_replace(fname,'(''| |-| JR$| SR$| III$| IV$)','','g')
                           = regexp_replace(patient_fname,'(''| |-| JR$| SR$| III$| IV$)','','g')                          AND
                         patient_dob IS NOT NULL                                                                           AND
                         patient_dob = dob                                                                                 AND 
                         (
                           (
                             subscriber_addr1 IS NOT NULL                                                 AND
			     addr1            IS NOT NULL                                                 AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(subscriber_addr1,'[ -]','','g') from 1 for 9)   AND
                             state IS NOT NULL                                                            AND
                             subscriber_state IS NOT NULL                                                 AND
                             state = subscriber_state
                           )
                           OR
                           (
                             patient_addr1 IS NOT NULL                                                    AND
			     addr1         IS NOT NULL                                                    AND
                             substring(regexp_replace(addr1,'[ -]','','g') from 1 for 9)
                               = substring(regexp_replace(patient_addr1,'[ -]','','g') from 1 for 9)      AND
                             state IS NOT NULL                                                            AND
                             patient_state IS NOT NULL                                                    AND
                             state = patient_state
                           )
                         )                                                                                                 AND
                         (ssn IS NULL or patient_ssn IS NULL)                                                                   THEN  64
                    ELSE                                                                                                              7
                    END) 
  ;


GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH UPDATING batch_insurance2_t TABLE : %', v_status;

-- END OF STEP 2 



-- STEP 3 - UPDATING SOME FIELDS - SECOND ROUND ;

RAISE NOTICE 'START UPDATING batch_insurance2_t TABLE - SECOND ROUND ';


  UPDATE batch_insurance2_t
  SET
    suppress_flag = (CASE WHEN quality = 7 THEN suppress_flag + 100000
                          ELSE                  suppress_flag
                          END)
  ;


GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH UPDATING batch_insurance2_t TABLE - SECOND ROUND: ';

-- END OF STEP 3 


PERFORM ml_upd_batch_status_insurance2_new_f(v_cust_id,v_batch_run_date,1,3,'batch_insurance2_t UPDATING END');



  return v_status;
END;
$$ LANGUAGE plpgsql;
